﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataCoreTechnologies.SqlServer
{
    public sealed partial class Database
    {
        private void Load2000_Schemas()
        {
            _schemas.Clear();

            string sql = @"select * from sysusers order by uid";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var schema = Factory.GetSchema(this);
                schema.Name = Convert.ToString(reader["name"]);
                schema.Id = Convert.ToInt32(reader["uid"]);
                AddSchema(schema);
            }
            reader.Close();
        }
        private void Load2000_SystemTypes()
        {
            string sql = @"select * from systypes where xtype = xusertype";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var systemType = Factory.GetSystemType(this);
                systemType.Name = Convert.ToString(reader["name"]);
                systemType.Id = Convert.ToInt32(reader["xtype"]);
                systemType.MaxLength = Convert.ToInt32(reader["length"]);
                systemType.Precision = Convert.ToInt32(reader["xprec"]);
                systemType.Scale = Convert.ToInt32(reader["xscale"]);
                systemType.CollationName = reader["collation"] == DBNull.Value ? "" : Convert.ToString(reader["collation"]);
                systemType.IsNullable = Convert.ToBoolean(reader["allownulls"]);

                //Assign a category to this data type
                if (systemType.Name == "bit" || systemType.Name == "tinyint" || systemType.Name == "smallint"
                    || systemType.Name == "int" || systemType.Name == "bigint" || systemType.Name == "numeric"
                    || systemType.Name == "decimal" || systemType.Name == "smallmoney" || systemType.Name == "money")
                {
                    systemType.Category = SystemTypeCategory.ExactNumerics;
                }
                else if (systemType.Name == "float" || systemType.Name == "real")
                {
                    systemType.Category = SystemTypeCategory.ApproximateNumerics;
                }
                else if (systemType.Name == "datetime" || systemType.Name == "smalldatetime")
                {
                    systemType.Category = SystemTypeCategory.DateAndTime;
                }
                else if (systemType.Name == "char" || systemType.Name == "varchar" || systemType.Name == "text")
                {
                    systemType.Category = SystemTypeCategory.CharacterStrings;
                }
                else if (systemType.Name == "nchar" || systemType.Name == "nvarchar" || systemType.Name == "ntext")
                {
                    systemType.Category = SystemTypeCategory.UnicodeCharacterStrings;
                }
                else if (systemType.Name == "binary" || systemType.Name == "varbinary" || systemType.Name == "image")
                {
                    systemType.Category = SystemTypeCategory.BinaryStrings;
                }
                else if (systemType.Name == "sql_variant" || systemType.Name == "timestamp"
                  || systemType.Name == "uniqueidentifier" || systemType.Name == "xml")
                {
                    systemType.Category = SystemTypeCategory.OtherDataTypes;
                }
                else { systemType.Category = SystemTypeCategory.Undefined; }

                AddSystemType(systemType);
            }
            reader.Close();
        }
        private void Load2000_UserTypes()
        {
            foreach (Schema schema in Schemas) schema.ClearUserTypes();

            string sql = @" select * from systypes 
                            where xtype <> xusertype
	                            and name <> 'sysname'
                         ";

            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var userType = Factory.GetUserType();
                userType.Name = Convert.ToString(reader["name"]);
                userType.Id = Convert.ToInt32(reader["xusertype"]);
                userType.MaxLength = Convert.ToInt32(reader["length"]);
                userType.Precision = Convert.ToInt32(reader["xprec"]);
                userType.Scale = Convert.ToInt32(reader["xscale"]);
                userType.CollationName = reader["collation"] == DBNull.Value ? "" : Convert.ToString(reader["collation"]);
                userType.IsNullable = Convert.ToBoolean(reader["allownulls"]);

                var rootSystemType = (from systemType in _systemTypes
                                      where systemType.Id == Convert.ToInt32(reader["xtype"])
                                      select systemType).Single<SystemType>();
                userType.SystemType = rootSystemType;

                var rootSchema = (from schema in Schemas
                                  where schema.Id == Convert.ToInt32(reader["uid"])
                                  select schema).Single<Schema>();

                userType.Schema = rootSchema;
                rootSchema.AddUserType(userType);
            }
            reader.Close();
        }
        private void Load2000_DatabaseObjects()
        {
            //http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx
            string sql = @"
                        select  sysobjects.*, syscomments.text as ddl
                        from sysobjects
                        left join syscomments on sysobjects.id = syscomments.id
                        where sysobjects.xtype in ('U', 'V', 'P', 'TF', 'IF', 'FN')
                            and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') = 0
                          ";
            SqlDataReader reader = Server.GetDataReader(sql);

            while (reader.Read())
            {
                var rootSchema = (from schema in Schemas
                                  where schema.Id == Convert.ToInt32(reader["uid"])
                                  select schema).Single<Schema>();

                string objectType = Convert.ToString(reader["type"]);
                objectType = objectType.Trim();

                switch (objectType)
                {
                    case "U":
                        var table = Factory.GetTable(rootSchema);
                        table.Name = Convert.ToString(reader["name"]);
                        table.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        table.ModifiedDate = null;
                        table.Id = Convert.ToInt32(reader["id"]);
                        table.Type = DatabaseObjectType.UserTable;

                        rootSchema.AddTable(table);
                        break;
                    case "V":
                        var view = Factory.GetView(rootSchema);
                        view.Name = Convert.ToString(reader["name"]);
                        view.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        view.ModifiedDate = null;
                        view.Id = Convert.ToInt32(reader["id"]);
                        view.Type = DatabaseObjectType.UserView;
                        view.DDL = Convert.ToString(reader["ddl"]);

                        rootSchema.AddView(view);
                        break;
                    case "P":
                        var storedProcedure = Factory.GetStoredProcedure(rootSchema);
                        storedProcedure.Name = Convert.ToString(reader["name"]);
                        storedProcedure.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        storedProcedure.ModifiedDate = null;
                        storedProcedure.Id = Convert.ToInt32(reader["id"]);
                        storedProcedure.Type = DatabaseObjectType.StoredProcedure;

                        rootSchema.AddStoredProcedure(storedProcedure);

                        //ToDo  Verify that in Sql 2000 every stored procedure only returns a integer
                        var parameter = Factory.GetParameter(storedProcedure);

                        parameter.Name = "Return Value";
                        parameter.DefaultValue = "";
                        parameter.IsOutput = true;
                        parameter.Order = 0;

                        parameter.SystemType = (from systemType in SystemTypes
                                                where systemType.Name == "int"
                                                select systemType).SingleOrDefault();

                        parameter.Length = parameter.SystemType.MaxLength;
                        parameter.Precision = parameter.SystemType.Precision;
                        parameter.Scale = parameter.SystemType.Scale;

                        storedProcedure.AddParameter(parameter);

                        //Load the DDL
                        storedProcedure.DDL = Convert.ToString(reader["ddl"]);

                        string ddl = Convert.ToString(reader["ddl"]);
                        while (ddl.Contains("\r\n"))
                        {
                            string line = ddl.Substring(0, ddl.IndexOf("\r\n"));
                            ddl = ddl.Substring(ddl.IndexOf("\r\n") + 2);
                            storedProcedure.AddCodeLine(line);
                        }
                        break;
                    case "IF":
                    case "FN":
                    case "TF":
                        var function = Factory.GetFunction(rootSchema);
                        rootSchema.AddFunction(function);

                        function.Name = Convert.ToString(reader["name"]);
                        function.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        function.ModifiedDate = null;
                        function.Id = Convert.ToInt32(reader["id"]);
                        if (Convert.ToString(reader["type"]) == "FN")
                            function.FunctionType = FunctionType.ScalarFunction;
                        else if (Convert.ToString(reader["type"]) == "IF")
                            function.FunctionType = FunctionType.TableFunction;
                        else
                            function.FunctionType = FunctionType.MultiStatement;

                        //Load the DDL
                        function.DDL = Convert.ToString(reader["ddl"]);

                        ddl = Convert.ToString(reader["ddl"]);
                        while (ddl.Contains("\r\n"))
                        {
                            string line = ddl.Substring(0, ddl.IndexOf("\r\n"));
                            ddl = ddl.Substring(ddl.IndexOf("\r\n") + 2);
                            function.AddCodeLine(line);
                        }
                        break;
                }
            }

            reader.Close();
        }
        private void Load2000_ColumnsAndParameters()
        {
            //http://msdn.microsoft.com/en-us/library/aa260398(SQL.80).aspx
            string sql = @" select 
	                            CAST(syscolumns.status & 128 AS bit) AS [IsIdentity],
	                            * 
                            from syscolumns
                            order by id, colorder
                            ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                #region Parameters
                var matchedStoredProc = (from schema in _schemas
                                         from storedProcedure in schema.StoredProcedures
                                         where storedProcedure.Id == Convert.ToInt32(reader["id"])
                                         select storedProcedure).SingleOrDefault();
                var matchedFunction = (from schema in _schemas
                                       from function in schema.Functions
                                       where function.Id == Convert.ToInt32(reader["id"])
                                       select function).SingleOrDefault();

                if (matchedStoredProc != null || matchedFunction != null)
                {
                    if ((Convert.ToString(reader["name"])).Trim() == "" ||
                        (Convert.ToString(reader["name"])).Substring(0, 1) == "@")
                    {
                        Parameter parameter = null;
                        if (matchedStoredProc != null)
                        {
                            parameter = Factory.GetParameter(matchedStoredProc);
                            matchedStoredProc.AddParameter(parameter);
                        }
                        else
                        {
                            parameter = Factory.GetParameter(matchedFunction);
                            matchedFunction.AddParameter(parameter);
                        }

                        if ((Convert.ToString(reader["name"])).Trim() == "")
                            parameter.Name = "Return Value";
                        else
                            parameter.Name = Convert.ToString(reader["name"]);
                        parameter.Length = Convert.ToInt32(reader["length"]);
                        parameter.Precision = Convert.ToInt32(reader["xprec"]);
                        parameter.Scale = Convert.ToInt32(reader["xscale"]);
                        parameter.Order = Convert.ToInt32(reader["colorder"]);

                        parameter.IsOutput = Convert.ToBoolean(reader["isoutparam"]);

                        parameter.SystemType = (from systemType in SystemTypes
                                                where systemType.Id == Convert.ToInt32(reader["xtype"])
                                                select systemType).SingleOrDefault();

                        parameter.UserType = (from schema in Schemas
                                              from customType in schema.UserTypes
                                              where customType.Id == Convert.ToInt32(reader["xusertype"])
                                              select customType).SingleOrDefault();
                    }
                }
                #endregion
                #region Column
                else
                {
                    var column = Factory.GetColumn();

                    column.Name = Convert.ToString(reader["name"]);
                    column.Order = Convert.ToInt32(reader["colorder"]);
                    if (reader["name"] == DBNull.Value)
                        column.CollationName = "";
                    else
                        column.CollationName = Convert.ToString(reader["name"]);

                    column.MaxLength = Convert.ToInt32(reader["length"]);
                    column.Precision = Convert.ToInt32(reader["xprec"]);
                    column.Scale = Convert.ToInt32(reader["xscale"]);
                    column.Id = Convert.ToInt32(reader["colid"]);

                    column.IsComputed = Convert.ToBoolean(reader["iscomputed"]);
                    column.IsNullable = Convert.ToBoolean(reader["isnullable"]);
                    column.IsPrimaryKey = Convert.ToBoolean(reader["IsIdentity"]);

                    column.SystemType = (from systemType in _systemTypes
                                         where systemType.Id == Convert.ToInt32(reader["xtype"])
                                         select systemType).SingleOrDefault();

                    var matchedUserType = (from schema in _schemas
                                           from userType in schema.UserTypes
                                           where userType.Id == Convert.ToInt32(reader["xusertype"])
                                           select userType).SingleOrDefault();
                    if (matchedUserType != null)
                        column.UserType = matchedUserType;

                    var matchedTable = (from schema in _schemas
                                        from table in schema.Tables
                                        where table.Id == Convert.ToInt32(reader["id"])
                                        select table).SingleOrDefault();
                    if (matchedTable != null)
                    {
                        column.Table = matchedTable;
                        matchedTable.AddColumn(column);
                    }
                    else
                    {
                        var matchedView = (from schema in _schemas
                                           from view in schema.Views
                                           where view.Id == Convert.ToInt32(reader["id"])
                                           select view).SingleOrDefault();
                        if (matchedView != null)
                        {
                            column.View = matchedView;
                            matchedView.AddColumn(column);
                        }
                    }
                }
                #endregion
            }
            reader.Close();
        }
        private void Load2000_CheckConstraintsAndDefaultValues()
        {
            string sql = @"
                        select sysobjects.name, sysobjects.id, sysobjects.xtype,
	                        sysobjects.crdate, sysobjects.info as columnId,
	                        sysobjects.parent_obj, sysobjects.refdate, 
	                        syscomments.text as formula
                        from sysobjects
                        inner join syscomments 
	                        on sysobjects.id = syscomments.id
                        where sysobjects.xtype in ('D', 'C')
	                        order by sysobjects.xtype
                         ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                if ((Convert.ToString(reader["xtype"])).Trim() == "C")
                {
                    var matchedColumn = (from schema in _schemas
                                         from table in schema.Tables
                                         where table.Id == Convert.ToInt32(reader["parent_obj"])
                                         from column in table.Columns
                                         where column.Id == Convert.ToInt32(reader["columnId"])
                                         select column).SingleOrDefault();
                    if (matchedColumn != null)
                    {
                        matchedColumn.CheckConstraint = Factory.GetCheckConstraint(matchedColumn);
                        matchedColumn.CheckConstraint.Name = Convert.ToString(reader["name"]);
                        matchedColumn.CheckConstraint.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        matchedColumn.CheckConstraint.ModifyDate = Convert.ToDateTime(reader["refdate"]);

                        string formula = Convert.ToString(reader["formula"]);
                        formula = formula.Trim();
                        while (formula.StartsWith("(") && formula.EndsWith(")"))
                            formula = formula.Substring(1, formula.Length - 2);

                        matchedColumn.CheckConstraint.Formula = formula;
                    }
                }
                else
                {
                    var matchedColumn = (from schema in _schemas
                                         from table in schema.Tables
                                         where table.Id == Convert.ToInt32(reader["parent_obj"])
                                         from column in table.Columns
                                         where column.Id == Convert.ToInt32(reader["columnId"])
                                         select column).SingleOrDefault();
                    if (matchedColumn != null)
                    {
                        matchedColumn.DefaultValue = Factory.GetDefaultValue(matchedColumn);
                        matchedColumn.DefaultValue.Name = Convert.ToString(reader["name"]);
                        matchedColumn.DefaultValue.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        matchedColumn.DefaultValue.ModifyDate = Convert.ToDateTime(reader["refdate"]);

                        string formula = Convert.ToString(reader["formula"]);
                        formula = formula.Trim();
                        while (formula.StartsWith("(") && formula.EndsWith(")"))
                            formula = formula.Substring(1, formula.Length - 2);

                        matchedColumn.DefaultValue.Formula = formula;
                    }

                }
            }
            reader.Close();

        }
        private void Load2000_Triggers()
        {
            string sql = @"
                        select sysobjects.name, sysobjects.id, sysobjects.xtype,
                            sysobjects.crdate, sysobjects.info as columnId,
                            sysobjects.parent_obj, sysobjects.refdate, 
                            syscomments.text as formula
                        from sysobjects
                        inner join syscomments 
                            on sysobjects.id = syscomments.id
                        where sysobjects.xtype in ('TR')
                            order by sysobjects.xtype
                          ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedTable = (from schema in _schemas
                                    from table in schema.Tables
                                    where table.Id == Convert.ToInt32(reader["parent_obj"])
                                    select table).SingleOrDefault();
                if (matchedTable != null)
                {
                   var trigger = Factory.GetTrigger(matchedTable);
                   matchedTable.AddTrigger(trigger);
                   trigger.Name = Convert.ToString(reader["name"]);
                   trigger.Id = Convert.ToInt32(reader["id"]);
                   trigger.CreateDate = Convert.ToDateTime(reader["crdate"]);
                   trigger.ModifyDate = Convert.ToDateTime(reader["refdate"]);
                   trigger.DDL = Convert.ToString(reader["formula"]);
                }
            }
            reader.Close();
        }
        private void Load2000_Indexes()
        {
            //http://msdn.microsoft.com/en-us/library/aa260413(SQL.80).aspx
            //Indexes
            string sql = @"
                        select name, 
	                        id as tableId, 
	                        indid as indexId, 
	                        CAST(case when indid = 1 then 1 else 0 end AS BIT) as isClustered,
	                        CAST(status & 2 AS bit) AS [IsUnique], 
	                        OBJECTPROPERTY(id,N'IsMSShipped') AS [IsMicrosoftShipped],
	                        CAST(COALESCE(INDEXPROPERTY(id,name,N'IsStatistics'), 0) AS BIT) AS [IsStatistics]
                        from sysindexes
                        where indid <> 0 and indid <> 255
	                        and OBJECTPROPERTY(id,N'IsMSShipped') = 0
	                        and CAST(COALESCE(INDEXPROPERTY(id,name,N'IsStatistics'), 0) AS BIT) = 0
                          ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedTable = (from schema in _schemas
                                    from table in schema.Tables
                                    where table.Id == Convert.ToInt32(reader["tableId"])
                                    select table).SingleOrDefault();
                if (matchedTable != null)
                {
                    var index =  Factory.GetIndex(matchedTable);
                    matchedTable.AddIndex(index);

                    index.Id = Convert.ToInt32(reader["indexId"]);
                    index.Name = Convert.ToString(reader["name"]);
                    index.IsUnique = Convert.ToBoolean(reader["IsUnique"]);
                    if (Convert.ToBoolean(reader["isClustered"]))
                        index.Type = IndexType.Clustered;
                    else
                        index.Type = IndexType.Nonclustered;
                    index.CreationDate = null;
                    index.ModifyDate = null;
                }
            }
            reader.Close();

            //http://msdn.microsoft.com/en-us/library/aa260412(SQL.80).aspx
            //Index Columns
            sql = @"
                    select 
	                    coalesce(Indexkey_Property(id, indid, colid, 'IsDescending'), 0) as IsDescending,
	                    * 
                    from sysindexkeys
                   ";
            reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedIndex = (from schema in _schemas
                                    from table in schema.Tables
                                    where table.Id == Convert.ToInt32(reader["id"])
                                    from index in table.Indexes
                                    where index.Id == Convert.ToInt32(reader["indid"])
                                    select index).SingleOrDefault();
                if (matchedIndex != null)
                {

                    var matchedColumn = (from column in matchedIndex.Table.Columns
                                         where column.Id == Convert.ToInt32(reader["colid"])
                                         select column).SingleOrDefault();
                    if (matchedColumn != null)
                    {
                        var indexColumn = Factory.GetIndexColumn(matchedIndex);
                        indexColumn.Column = matchedColumn;
                        matchedIndex.AddIndexColumn(indexColumn);

                        indexColumn.Order = Convert.ToInt32(reader["keyno"]);
                        indexColumn.IsIncluded = true;
                        indexColumn.IsDescending = Convert.ToBoolean(reader["IsDescending"]);
                    }
                }
            }
            reader.Close();
        }
        private void Load2000_PrimaryKeys()
        {
            string sql = @"
                            select OBJECTPROPERTY(id, N'IsMSShipped') AS IsMSShipped, * 
                            from sysobjects where xtype = 'PK'
                          ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedTable = (from schema in _schemas
                                    from table in schema.Tables
                                    where table.Id == Convert.ToInt32(reader["parent_obj"])
                                    select table).SingleOrDefault();
                if (matchedTable != null)
                {
                    var matchedIndex = (from index in matchedTable.Indexes
                                        where index.Id == Convert.ToInt32(reader["uid"])
                                        select index).SingleOrDefault();
                    if (matchedIndex != null)
                    {
                        matchedTable.PrimaryKey = Factory.GetPrimaryKey(matchedTable);
                        matchedTable.PrimaryKey.Index = matchedIndex;
                        matchedIndex.PrimaryKey = matchedTable.PrimaryKey;

                        matchedTable.PrimaryKey.Id = Convert.ToInt32(reader["id"]);
                        matchedTable.PrimaryKey.Name = Convert.ToString(reader["name"]);
                        matchedTable.PrimaryKey.CreateDate = Convert.ToDateTime(reader["crdate"]);
                        matchedTable.PrimaryKey.ModifyDate = Convert.ToDateTime(reader["refdate"]);
                        matchedTable.PrimaryKey.IsMicrosoftShipped = Convert.ToBoolean(reader["IsMSShipped"]);
                    }
                }
            }
            reader.Close();
        }
        private void Load2000_ForeignKeys()
        {
            //http://msdn.microsoft.com/en-us/library/aa260409(SQL.80).aspx
            string sql = @"
                        select soFKey.name as ForeignKeyName, 
                            soFKey.id as ForeignKeyId,
                            soSourceTable.Name as SourceTableName,
                            soSourceTable.Id as SourceTableId,
                            FK.fkey as SourceColumnId,
                            soReferredTable.Name as ReferredTableName,
                            soReferredTable.Id as ReferredTableId,
                            FK.rkey as ReferredColumnId,
                            soFKey.crdate ForeignKeyCreateDate
                        from sysobjects as soFKey
                        inner join sysforeignkeys as FK
                            on soFKey.id = FK.constid
                        inner join sysobjects as soSourceTable
                            on FK.fkeyId = soSourceTable.id
                        inner join sysobjects as soReferredTable
                            on FK.rkeyId = soReferredTable.id
                        where soFKey.xtype = 'F'
                          ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var rootTable = (from schema in _schemas
                                 from table in schema.Tables
                                 where table.Id == Convert.ToInt32(reader["SourceTableId"])
                                 select table).SingleOrDefault();
                if (rootTable != null)
                {
                    var fk = Factory.GetForeignKey(rootTable);
                    rootTable.AddForeignKey(fk);

                    fk.Name = Convert.ToString(reader["ForeignKeyName"]);
                    //fk.CreateDate
                    fk.Id = Convert.ToInt32(reader["ForeignKeyId"]);

                    Column parentColumn = (from column in rootTable.Columns
                                           where column.Id == Convert.ToInt32(reader["SourceColumnId"])
                                           select column).SingleOrDefault();
                    Column referencedColumn = (from schema in _schemas
                                               from table in schema.Tables
                                               where table.Id == Convert.ToInt32(reader["ReferredTableId"])
                                               from column in table.Columns
                                               where column.Id == Convert.ToInt32(reader["ReferredColumnId"])
                                               select column).SingleOrDefault();
                    
                    parentColumn.IsForeignKey = true;
                    fk.AddColumns(Factory.GetForeignKeyColumns(parentColumn, referencedColumn));
                }
            }
            reader.Close();
        }
        private void Load2000_ExtendedProperties()
        {
            //http://msdn.microsoft.com/en-us/library/aa258890(SQL.80).aspx
            #region Level 0
            SqlDataReader reader;

            var userTypes = (from schema in _schemas
                             from userType in schema.UserTypes
                             select userType);
            string sql = "";
            if (userTypes.Count() > 0)
            {
                foreach (UserType userType in userTypes)
                {
                    sql += @"UNION SELECT * FROM ::fn_listextendedproperty(DEFAULT, 'TYPE', '" +
                        userType.Name + @"', DEFAULT, DEFAULT, DEFAULT, DEFAULT) ";
                }
                sql = sql.Substring(6);
                reader = Server.GetDataReader(sql);
                while (reader.Read())
                {
                    string name = Convert.ToString(reader["name"]);
                    string value = Convert.ToString(reader["value"]);
                    var matchedUserType = (from schema in _schemas
                                           from userType in schema.UserTypes
                                           where userType.Name == Convert.ToString(reader["objname"])
                                           select userType).SingleOrDefault();
                    if (matchedUserType != null)
                    {
                        var extProperty = Factory.GetExtendedProperty(name, value);
                        extProperty.UserType = matchedUserType;
                        matchedUserType.AddExtendedProperty(extProperty);
                    }
                }
                reader.Close();
            }
            #endregion
            #region Level 1
            foreach (Schema schema in _schemas)
            {
                sql = @"SELECT * FROM ::fn_listextendedproperty(NULL, 'USER', '" +
                    schema.Name + @"', 'TABLE', NULL, NULL, NULL) ";
                sql += @"UNION SELECT * FROM ::fn_listextendedproperty(NULL, 'USER', '" +
                    schema.Name + @"', 'VIEW', NULL, NULL, NULL)";
                sql += @"UNION SELECT * FROM ::fn_listextendedproperty(NULL, 'USER', '" +
                    schema.Name + @"', 'PROCEDURE', NULL, NULL, NULL)";
                sql += @"UNION SELECT * FROM ::fn_listextendedproperty(NULL, 'USER', '" +
                    schema.Name + @"', 'FUNCTION', NULL, NULL, NULL)";
                sql += @"UNION SELECT * FROM ::fn_listextendedproperty(NULL, 'USER', '" +
                    schema.Name + @"', 'DEFAULT', NULL, NULL, NULL)";
                sql += @"UNION SELECT * FROM ::fn_listextendedproperty(NULL, 'USER', '" +
                    schema.Name + @"', 'RULE', NULL, NULL, NULL)";
                reader = Server.GetDataReader(sql);
                while (reader.Read())
                {
                    string name = Convert.ToString(reader["name"]);
                    string value = Convert.ToString(reader["value"]);
                    var extProperty = Factory.GetExtendedProperty(name, value);

                    switch (Convert.ToString(reader["objtype"]))
                    {
                        case "TABLE":
                            var matchedTable = (from table in schema.Tables
                                                where table.Name == Convert.ToString(reader["objname"])
                                                select table).SingleOrDefault();
                            if (matchedTable != null)
                            {
                                matchedTable.AddExtendedProperty(extProperty);
                                extProperty.Table = matchedTable;
                            }
                            break;
                        case "VIEW":
                            var matchedView = (from view in schema.Views
                                               where view.Name == Convert.ToString(reader["objname"])
                                               select view).SingleOrDefault();
                            if (matchedView != null)
                            {
                                matchedView.AddExtendedProperty(extProperty);
                                extProperty.View = matchedView;
                            }
                            break;
                        case "PROCEDURE":
                            var matchedSProc = (from sproc in schema.StoredProcedures
                                                where sproc.Name == Convert.ToString(reader["objname"])
                                                select sproc).SingleOrDefault();
                            if (matchedSProc != null)
                            {
                                matchedSProc.AddExtendedProperty(extProperty);
                                extProperty.StoredProcedure = matchedSProc;
                            }
                            break;
                        case "FUNCTION":
                            var matchedFunction = (from function in schema.Functions
                                                   where function.Name == Convert.ToString(reader["objname"])
                                                   select function).SingleOrDefault();
                            if (matchedFunction != null)
                            {
                                matchedFunction.AddExtendedProperty(extProperty);
                                extProperty.Function = matchedFunction;
                            }
                            break;
                    }
                }
                reader.Close();
            }
            #endregion
            #region Level 2
            var columns = (from schema in _schemas
                           from table in schema.Tables
                           from column in table.Columns
                           select column);
            sql = "";
            foreach (Column column in columns)
            {
                sql += @"UNION SELECT " + column.Table.Schema.Id.ToString() + @" AS SchemaId, " +
                    column.Table.Id + @" AS TableId, * FROM ::fn_listextendedproperty(DEFAULT, 'USER', '" +
                    column.Table.Schema.Name + @"', 'TABLE', '" +
                    column.Table.Name + @"', 'COLUMN', DEFAULT) ";
                sql += @"UNION SELECT " + column.Table.Schema.Id.ToString() + @" AS SchemaId, " +
                    column.Table.Id + @" AS TableId, * FROM ::fn_listextendedproperty(DEFAULT, 'USER', '" +
                    column.Table.Schema.Name + @"', 'TABLE', '" +
                    column.Table.Name + @"', 'INDEX', DEFAULT) ";
            }
            sql = sql.Substring(6);

            reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedSchema = (from schema in _schemas
                                     where schema.Id == Convert.ToInt32(reader["SchemaId"])
                                     select schema).Single();
                var matchedTable = (from table in matchedSchema.Tables
                                    where table.Id == Convert.ToInt32(reader["TableId"])
                                    select table).Single();
                string name = Convert.ToString(reader["name"]);
                string value = Convert.ToString(reader["value"]);
                var extendedProperty = Factory.GetExtendedProperty(name, value);

                if (Convert.ToString(reader["objtype"]) == "COLUMN")
                {
                    var matchedColumn = (from column in matchedTable.Columns
                                         where column.Name == Convert.ToString(reader["objname"])
                                         select column).Single();
                    extendedProperty.Column = matchedColumn;
                    matchedColumn.AddExtendedProperty(extendedProperty);
                }
                else if (Convert.ToString(reader["objtype"]) == "INDEX")
                {
                    var matchedIndex = (from index in matchedTable.Indexes
                                        where index.Name == Convert.ToString(reader["objname"])
                                        select index).Single();
                    extendedProperty.Index = matchedIndex;
                    matchedIndex.AddExtendedProperty(extendedProperty);
                }
            }
            reader.Close();
            #endregion
        }
        private void Load2000_ObjectSizes()
        {
            //Database
            Load2005_DatabaseSize();
            //Tables and Indexes
            Load2005_TableSizes();
        }
    }
}
